Cache Table Design for Disease Surveillance System

Introduction The Real-time Outbreak and Disease Surveillance (RODS) system is a public health surveillance system that has been developed by the Center for Biomedical Informatics at the University of Pittsburgh and deployed since 1999 in western Pennsylvania. RODS receives HL-7 ADT (admission, discharge and transfer) messages from over 45 hospitals and clinics within Utah State and western Pennsylvania. It displays spatial-temporal plots of patients presenting with seven key prodromes through web interface. The large data volume (about ten thousands records per day) in the database results in a lengthened response time when users request an online analysis. The slow response time is that users’ queries often include joins of several huge tables to perform a statistical analysis, e.g., computing daily percentage of patients with a particular prodrome in a region for one month period. To meet these challenges, we introduce our scheme that employs multiple cache tables based on user’s demand to store pre-computed analytical results often need. Using the cache-table scheme dramatically increases the speed of users’ queries, decreases the CPU load (often caused by multiple complex queries), and provides realtime output. We also discussed the difference between our approach and data warehouse-based online analytical processing (OLAP).